﻿using System;
using System.Collections.Generic;
using System.Text;

namespace HZ.Supervise.DAL
{
    public class DAL_PreWarning
    {
        public static System.Data.DataTable GetDrugPreWarning(Model.DrugPreWarning drugprewarning, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sql = new StringBuilder();
            if (drugprewarning != null)
            {
                if (drugprewarning.IsUsing == true)
                {
                    sql.Append(string.Format("SELECT jg.TB_DrugPreWarning.*, dbo.TB_Hospital.HospitalName,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,"));
                    sql.Append(string.Format("dbo.View_ProcureCatalog.Outlookc,dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Sort.SortName FROM jg.TB_DrugPreWarning "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital ON jg.TB_DrugPreWarning.HospitalID = dbo.TB_Hospital.HospitalID "));
                    sql.Append(string.Format("LEFT JOIN dbo.View_ProcureCatalog on jg.TB_DrugPreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsID "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Sort on jg.TB_DrugPreWarning.sortid=dbo.TB_Sort.sortid where jg.TB_DrugPreWarning.IsUsing={0} ORDER BY jg.TB_DrugPreWarning.AddTime DESC", 1));
                }
                else
                {

                    sql.Append(string.Format("SELECT jg.TB_DrugPreWarning.*, dbo.TB_Hospital.HospitalName,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,"));
                    sql.Append(string.Format("dbo.View_ProcureCatalog.Outlookc,dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Sort.SortName FROM jg.TB_DrugPreWarning "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital ON jg.TB_DrugPreWarning.HospitalID = dbo.TB_Hospital.HospitalID "));
                    sql.Append(string.Format("LEFT JOIN dbo.View_ProcureCatalog on jg.TB_DrugPreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsID "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Sort on jg.TB_DrugPreWarning.sortid=dbo.TB_Sort.sortid where jg.TB_DrugPreWarning.IsUsing={0} ORDER BY jg.TB_DrugPreWarning.AddTime DESC", 0));
                }
            }
            else
            {
                sql.Append(string.Format("SELECT jg.TB_DrugPreWarning.*, dbo.TB_Hospital.HospitalName,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,"));
                sql.Append(string.Format("dbo.View_ProcureCatalog.Outlookc,dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Sort.SortName FROM jg.TB_DrugPreWarning "));
                sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital ON jg.TB_DrugPreWarning.HospitalID = dbo.TB_Hospital.HospitalID "));
                sql.Append(string.Format("LEFT JOIN dbo.View_ProcureCatalog on jg.TB_DrugPreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsID "));
                sql.Append(string.Format("LEFT JOIN dbo.TB_Sort on jg.TB_DrugPreWarning.sortid=dbo.TB_Sort.sortid ORDER BY jg.TB_DrugPreWarning.AddTime DESC"));
            }

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql.ToString(), pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }

        /// <summary>
        /// 采购状态或配送状态
        /// </summary>
        /// <param name="tradeprewarning"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetTradePreWarning(HZ.Supervise.Model.TradePreWarning tradeprewarning, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sql = new StringBuilder();
            if (tradeprewarning != null)
            {
                if (tradeprewarning.IsUsing == true)
                {
                    sql.Append(string.Format("SELECT jg.TB_TradePreWarning.*,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,dbo.View_ProcureCatalog.Outlookc,dbo.TB_Sort.SortName, "));
                    sql.Append(string.Format("dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Hospital.HospitalName,dbo.TB_Company.CompanyName FROM jg.TB_TradePreWarning LEFT JOIN dbo.View_ProcureCatalog "));
                    sql.Append(string.Format("on jg.TB_TradePreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsId LEFT JOIN TB_Sort on jg.TB_TradePreWarning.sortid=TB_Sort.sortid "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital on jg.TB_TradePreWarning.HospitalId=dbo.TB_Hospital.HospitalId LEFT JOIN dbo.TB_Company on jg.TB_TradePreWarning.CompanyID_PS=dbo.TB_Company.CompanyID "));
                    sql.Append(string.Format("where jg.TB_TradePreWarning.IsUsing={0} ORDER BY jg.TB_TradePreWarning.AddTime DESC", 1));
                }
                else
                {
                    sql.Append(string.Format("SELECT jg.TB_TradePreWarning.*,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,dbo.View_ProcureCatalog.Outlookc,dbo.TB_Sort.SortName, "));
                    sql.Append(string.Format("dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Hospital.HospitalName,dbo.TB_Company.CompanyName FROM jg.TB_TradePreWarning LEFT JOIN dbo.View_ProcureCatalog "));
                    sql.Append(string.Format("on jg.TB_TradePreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsId LEFT JOIN TB_Sort on jg.TB_TradePreWarning.sortid=TB_Sort.sortid  "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital on jg.TB_TradePreWarning.HospitalId=dbo.TB_Hospital.HospitalId LEFT JOIN dbo.TB_Company on jg.TB_TradePreWarning.CompanyID_PS=dbo.TB_Company.CompanyID "));
                    sql.Append(string.Format("where jg.TB_TradePreWarning.IsUsing={0} ORDER BY jg.TB_TradePreWarning.AddTime DESC", 0));
                }

            }
            else
            {
                sql.Append(string.Format("SELECT jg.TB_TradePreWarning.*,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,dbo.View_ProcureCatalog.Outlookc,dbo.TB_Sort.SortName, "));
                sql.Append(string.Format("dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Hospital.HospitalName,dbo.TB_Company.CompanyName FROM jg.TB_TradePreWarning LEFT JOIN dbo.View_ProcureCatalog "));
                sql.Append(string.Format("on jg.TB_TradePreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsId LEFT JOIN TB_Sort on jg.TB_TradePreWarning.sortid=TB_Sort.sortid "));
                sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital on jg.TB_TradePreWarning.HospitalId=dbo.TB_Hospital.HospitalId LEFT JOIN dbo.TB_Company on jg.TB_TradePreWarning.CompanyID_PS=dbo.TB_Company.CompanyID "));
                sql.Append(string.Format(" ORDER BY jg.TB_TradePreWarning.AddTime DESC"));
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql.ToString(), pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }


        public static System.Data.DataTable GetTradePreWarning_CL(HZ.Supervise.Model.TradePreWarning tradeprewarning, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sql = new StringBuilder();
            if (tradeprewarning != null)
            {
                if (tradeprewarning.IsUsing == true)
                {
                    sql.Append(string.Format("SELECT jg.TB_TradePreWarning.*,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,dbo.View_ProcureCatalog.Outlookc,dbo.TB_Sort.SortName, "));
                    sql.Append(string.Format("dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Hospital.HospitalName,dbo.TB_Company.CompanyName FROM jg.TB_TradePreWarning LEFT JOIN dbo.View_ProcureCatalog "));
                    sql.Append(string.Format("on jg.TB_TradePreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsId LEFT JOIN TB_Sort on jg.TB_TradePreWarning.sortid=TB_Sort.sortid "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital on jg.TB_TradePreWarning.HospitalId=dbo.TB_Hospital.HospitalId LEFT JOIN dbo.TB_Company on jg.TB_TradePreWarning.CompanyID_PS=dbo.TB_Company.CompanyID "));
                    sql.Append(string.Format("where datediff(d,jg.TB_TradePreWarning.AddTime,getdate())>jg.TB_TradePreWarning.WarningDate and jg.TB_TradePreWarning.IsUsing={0} ORDER BY jg.TB_TradePreWarning.AddTime DESC", 1));
                }
                else
                {
                    sql.Append(string.Format("SELECT jg.TB_TradePreWarning.*,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,dbo.View_ProcureCatalog.Outlookc,dbo.TB_Sort.SortName, "));
                    sql.Append(string.Format("dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Hospital.HospitalName,dbo.TB_Company.CompanyName FROM jg.TB_TradePreWarning LEFT JOIN dbo.View_ProcureCatalog "));
                    sql.Append(string.Format("on jg.TB_TradePreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsId LEFT JOIN TB_Sort on jg.TB_TradePreWarning.sortid=TB_Sort.sortid  "));
                    sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital on jg.TB_TradePreWarning.HospitalId=dbo.TB_Hospital.HospitalId LEFT JOIN dbo.TB_Company on jg.TB_TradePreWarning.CompanyID_PS=dbo.TB_Company.CompanyID "));
                    sql.Append(string.Format("where datediff(d,jg.TB_TradePreWarning.AddTime,getdate())>jg.TB_TradePreWarning.WarningDate and jg.TB_TradePreWarning.IsUsing={0} ORDER BY jg.TB_TradePreWarning.AddTime DESC", 0));
                }

            }
            else
            {
                sql.Append(string.Format("SELECT jg.TB_TradePreWarning.*,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,dbo.View_ProcureCatalog.Outlookc,dbo.TB_Sort.SortName, "));
                sql.Append(string.Format("dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Hospital.HospitalName,dbo.TB_Company.CompanyName FROM jg.TB_TradePreWarning LEFT JOIN dbo.View_ProcureCatalog "));
                sql.Append(string.Format("on jg.TB_TradePreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsId LEFT JOIN TB_Sort on jg.TB_TradePreWarning.sortid=TB_Sort.sortid "));
                sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital on jg.TB_TradePreWarning.HospitalId=dbo.TB_Hospital.HospitalId LEFT JOIN dbo.TB_Company on jg.TB_TradePreWarning.CompanyID_PS=dbo.TB_Company.CompanyID "));
                sql.Append(string.Format("ORDER BY jg.TB_TradePreWarning.AddTime DESC "));
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql.ToString(), pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        public static System.Data.DataTable GetDrugPreWarning_CL(HZ.Supervise.Model.DrugPreWarning drugprewarning, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sql = new StringBuilder();
            if (drugprewarning != null)
            {
                if (drugprewarning.IsUsing == true)
                {
                    sql.Append(string.Format("select A.* ,B.ProductName,B.HospitalName,B.CompanyName_PS,B.MedicineModelName,B.Outlookc from jg.TB_DrugPreWarning as A inner join dbo.View_PurchaseOrderDetail as B on A.AreaID = B.AreaID and A.ProcureCatalogID = B.ProcureCatalogID and A.HospitalID = B.HospitalID and a.WarningCount < B.PurchaseCount and A.WarningAmount < B.Amount and datediff(d,A.AddTime,getdate())>A.WarningDate and A.IsUsing = {0} ORDER BY A.AddTime DESC", 1));
                }
                else
                {

                    sql.Append(string.Format("select A.* ,B.ProductName,B.HospitalName,B.CompanyName_PS,B.MedicineModelName,B.Outlookc from jg.TB_DrugPreWarning as A inner join dbo.View_PurchaseOrderDetail as B on A.AreaID = B.AreaID and A.ProcureCatalogID = B.ProcureCatalogID and A.HospitalID = B.HospitalID and a.WarningCount < B.PurchaseCount and A.WarningAmount < B.Amount and datediff(d,A.AddTime,getdate())>A.WarningDate and A.IsUsing = {0} ORDER BY A.AddTime DESC", 0));
                }

            }
            else
            {
                sql.Append(string.Format("SELECT jg.TB_DrugPreWarning.*, dbo.TB_Hospital.HospitalName,dbo.View_ProcureCatalog.ProductName,dbo.View_ProcureCatalog.MedicinemodelName,"));
                sql.Append(string.Format("dbo.View_ProcureCatalog.Outlookc,dbo.View_ProcureCatalog.Pack,dbo.View_ProcureCatalog.CompanyName_SC,dbo.TB_Sort.SortName FROM jg.TB_DrugPreWarning "));
                sql.Append(string.Format("LEFT JOIN dbo.TB_Hospital ON jg.TB_DrugPreWarning.HospitalID = dbo.TB_Hospital.HospitalID "));
                sql.Append(string.Format("LEFT JOIN dbo.View_ProcureCatalog on jg.TB_DrugPreWarning.ProcureCatalogID=dbo.View_ProcureCatalog.GoodsID "));
                sql.Append(string.Format("LEFT JOIN dbo.TB_Sort on jg.TB_DrugPreWarning.sortid=dbo.TB_Sort.sortid ORDER BY jg.TB_DrugPreWarning.AddTime DESC"));
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql.ToString(), pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        public static System.Data.DataTable GetWarningData(HZ.Supervise.Model.WarningData warningData, int currentpage, int pagesize, out int totalrecord)
        {
            string wherestr = DAL_Helper.GetWhereCondition(warningData, false);
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format("select AreaID,AreaName,SortId,SortName,ProcureCatalogID,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningCount,WarningAmount,WarningDate,WarningType,WarningLevel from jg.TB_WarningData  where 1=1 {0} group by AreaID,AreaName,SortId,SortName,ProcureCatalogID,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningCount,WarningAmount,WarningDate,WarningType,WarningLevel order by AreaName,WarningType asc", wherestr));
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql.ToString(), pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 装载预警数据
        /// </summary>
        public static void Insert_WarningData()
        {
            //string sql = "Delete jg.TB_WarningData";
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);//清空预警数据表

            System.Data.DataTable dtDrugPre = HZ.Data.DbHelperFactory.Create().ExecuteTable("select * from jg.TB_DrugPreWarning where IsUsing=1");
            if (dtDrugPre.Rows.Count > 0)
            {
                System.Text.StringBuilder sqlDrugs = new System.Text.StringBuilder();
                int js = 0, gs = 0;//计数器
                //计算出循环的次数
                int PerformViews = dtDrugPre.Rows.Count / 10;

                if (dtDrugPre.Rows.Count % 10 != 0)
                {
                    PerformViews++;
                }

                if (PerformViews > 1)
                {
                    int performziJian = PerformViews - 1;
                    for (int i = 0; i < dtDrugPre.Rows.Count; i++) //
                    {
                        string _AreaID = dtDrugPre.Rows[i]["AreaID"].ToString();
                        string _SortID = dtDrugPre.Rows[i]["SortID"].ToString();
                        string _ProcureCatalogID = dtDrugPre.Rows[i]["ProcureCatalogID"].ToString();
                        string _HospitalID = dtDrugPre.Rows[i]["HospitalID"].ToString();

                        int _WarningCount = 0;
                        if (HZ.Fun.IsInt(dtDrugPre.Rows[i]["WarningCount"].ToString())) //预警采购数量
                        {
                            _WarningCount = int.Parse(dtDrugPre.Rows[i]["WarningCount"].ToString());
                        }

                        decimal _WarningAmount = 0;
                        if (HZ.Fun.IsFloat(dtDrugPre.Rows[i]["WarningAmount"].ToString()))//预警金额
                        {
                            _WarningAmount = decimal.Parse(dtDrugPre.Rows[i]["WarningAmount"].ToString());
                        }

                        string _WarningLevel = dtDrugPre.Rows[i]["WarningLevel"].ToString();
                        int _WarningDate = int.Parse(dtDrugPre.Rows[i]["WarningDate"].ToString());

                        sqlDrugs.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,SortId,SortName,ProcureCatalogID,");
                        sqlDrugs.AppendFormat("HospitalID,HospitalName,OrderId,Amount,PurchaseCount,WarningCount,WarningAmount,WarningLevel,WarningDate,[Type],AddTime)");
                        sqlDrugs.AppendFormat("select a.AreaID,b.AreaName,a.SortId,a.SortName,a.ProcureCatalogID,");
                        sqlDrugs.AppendFormat("a.HospitalID,a.HospitalName,a.OrderID,a.Amount,a.PurchaseCount,{0},{1},'{2}',{3},0,PlanTime from ", _WarningCount, _WarningAmount, _WarningLevel, _WarningDate);
                        sqlDrugs.AppendFormat("View_PurchaseOrderDetail a inner join TB_Area b on a.AreaID=b.AreaID where a.PlanTime<='{0}' ", System.DateTime.Now.AddDays(-1 * _WarningDate));


                        if (_HospitalID != "所有医院")
                        {
                            _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                            sqlDrugs.AppendFormat(" and a.HospitalID IN({0})", _HospitalID);
                        }

                        if (_ProcureCatalogID != "所有药品") { sqlDrugs.AppendFormat(" and a.ProcureCatalogID={0}", _ProcureCatalogID); }

                        sqlDrugs.AppendFormat(" and a.AreaID='{0}'", _AreaID);

                        if (_WarningCount > 0 && _WarningAmount > 0)
                        {
                            sqlDrugs.AppendFormat(" and (a.PurchaseCount>{0} or a.Amount>{1}); ", _WarningCount, _WarningAmount);
                        }
                        else
                        {
                            if (_WarningCount > 0)
                            {
                                sqlDrugs.AppendFormat(" and a.PurchaseCount>{0};", _WarningCount);
                            }
                            if (_WarningAmount > 0)
                            {
                                sqlDrugs.AppendFormat(" and a.Amount>{0};", _WarningAmount);
                            }
                        }


                        js++;
                        if (gs == performziJian)
                        {
                            if (i == dtDrugPre.Rows.Count - 1)
                            {
                                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sqlDrugs.ToString());
                                js = 0;
                                sqlDrugs.Remove(0, sqlDrugs.Length);
                            }
                        }
                        else
                        {
                            if (js == 10)
                            {
                                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sqlDrugs.ToString());
                                gs++;
                                js = 0;
                                sqlDrugs.Remove(0, sqlDrugs.Length);
                            }
                        }

                    }
                }
                else
                {
                    for (int i = 0; i < dtDrugPre.Rows.Count; i++)
                    {
                        string _AreaID = dtDrugPre.Rows[i]["AreaID"].ToString();
                        string _SortID = dtDrugPre.Rows[i]["SortID"].ToString();
                        string _ProcureCatalogID = dtDrugPre.Rows[i]["ProcureCatalogID"].ToString();
                        string _HospitalID = dtDrugPre.Rows[i]["HospitalID"].ToString();
                        int _WarningCount = 0;
                        if (HZ.Fun.IsInt(dtDrugPre.Rows[i]["WarningCount"].ToString()))
                        {
                            _WarningCount = int.Parse(dtDrugPre.Rows[i]["WarningCount"].ToString());
                        }
                        decimal _WarningAmount = 0;
                        if (HZ.Fun.IsFloat(dtDrugPre.Rows[i]["WarningAmount"].ToString()))
                        {
                            _WarningAmount = decimal.Parse(dtDrugPre.Rows[i]["WarningAmount"].ToString());
                        }
                        string _WarningLevel = dtDrugPre.Rows[i]["WarningLevel"].ToString();
                        int _WarningDate = int.Parse(dtDrugPre.Rows[i]["WarningDate"].ToString());

                        sqlDrugs.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,SortId,SortName,ProcureCatalogID,");
                        sqlDrugs.AppendFormat("HospitalID,HospitalName,OrderId,Amount,PurchaseCount,WarningCount,WarningAmount,WarningLevel,WarningDate,[Type],AddTime)");
                        sqlDrugs.AppendFormat("select a.AreaID,b.AreaName,a.SortId,a.SortName,a.ProcureCatalogID,");
                        sqlDrugs.AppendFormat("a.HospitalID,a.HospitalName,a.OrderID,a.Amount,a.PurchaseCount,{0},{1},'{2}',{3},0,PlanTime from ", _WarningCount, _WarningAmount, _WarningLevel, _WarningDate);
                        sqlDrugs.AppendFormat("View_PurchaseOrderDetail a inner join TB_Area b on a.AreaID=b.AreaID where a.PlanTime<='{0}' ", System.DateTime.Now.AddDays(-1 * _WarningDate));

                        if (_HospitalID != "所有医院")
                        {
                            _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                            sqlDrugs.AppendFormat(" and a.HospitalID IN ({0})", _HospitalID);
                        }

                        if (_ProcureCatalogID != "所有药品") { sqlDrugs.AppendFormat(" and a.ProcureCatalogID={0}", _ProcureCatalogID); }

                        sqlDrugs.AppendFormat(" and a.AreaID='{0}'", _AreaID);

                        if (_WarningCount > 0 && _WarningAmount > 0)
                        {
                            sqlDrugs.AppendFormat(" and (a.PurchaseCount>{0} or a.Amount>{1});", _WarningCount, _WarningAmount);
                        }
                        else
                        {
                            if (_WarningCount > 0)
                            {
                                sqlDrugs.AppendFormat(" and a.PurchaseCount>{0};", _WarningCount);
                            }
                            if (_WarningAmount > 0)
                            {
                                sqlDrugs.AppendFormat(" and a.Amount>{0};", _WarningAmount);
                            }
                        }
                    }

                    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sqlDrugs.ToString());
                }

            }

            System.Data.DataTable dtTradePre = HZ.Data.DbHelperFactory.Create().ExecuteTable("select * from jg.TB_TradePreWarning where IsUsing=1");

            if (dtTradePre.Rows.Count > 0)
            {
                System.Text.StringBuilder sqlTradePre = new System.Text.StringBuilder();

                int js = 0, gs = 0;//计数器

                int PerformViews = dtTradePre.Rows.Count / 10;
                if (dtTradePre.Rows.Count % 10 != 0)
                {
                    PerformViews++;
                }

                if (PerformViews > 1)
                {
                    int performziJian = PerformViews - 1;
                    for (int i = 0; i < dtTradePre.Rows.Count; i++)
                    {
                        string _AreaID = dtTradePre.Rows[i]["AreaID"].ToString();  //区域编号
                        string _SortID = dtTradePre.Rows[i]["SortID"].ToString();  //类别编号
                        string _ProcureCatalogID = dtTradePre.Rows[i]["ProcureCatalogID"].ToString();//药品编号
                        string _HospitalID = dtTradePre.Rows[i]["HospitalID"].ToString(); //医院编号
                        string _CompanyNameId = dtTradePre.Rows[i]["CompanyId_PS"].ToString(); //企业编号
                        string _WarningLevel = dtTradePre.Rows[i]["WarningLevel"].ToString();  //预警级别
                        int _WarningDate = int.Parse(dtTradePre.Rows[i]["WarningDate"].ToString());//预警天数
                        string _WarningType = dtTradePre.Rows[i]["WarningType"].ToString();//预警类型

                        switch (_WarningType)
                        {
                            case "0":   //医院已提交采购单企业未确认
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,SortId,SortName,ProcureCatalogID,");
                                sqlTradePre.AppendFormat("HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,OrderId,Amount,PurchaseCount,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.SortId,a.SortName,a.ProcureCatalogID,a.HospitalID,a.HospitalName, ");
                                sqlTradePre.AppendFormat("a.CompanyId_PS,a.CompanyName_PS,a.OrderId,a.Amount,a.PurchaseCount,{0},{1},{2},1,'{3}' from ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("View_PurchaseOrderDetail a inner join TB_Area b on a.AreaID=b.AreaID where a.OrderDetailState=1 and a.StartTime <='{0}'", DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0}) ", _HospitalID);
                                }
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyId_PS IN({0})", _CompanyNameId);
                                }

                                if (_ProcureCatalogID != "所有药品") { sqlTradePre.AppendFormat(" and a.ProcureCatalogID={0}", _ProcureCatalogID); }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "1":  //企业配送定单医院未入库
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,SortId,SortName,ProcureCatalogID,HospitalID,HospitalName,");
                                sqlTradePre.AppendFormat("CompanyId_PS,CompanyName_PS,OrderId,Amount,PurchaseCount,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select AreaID,AreaName,SortId,SortName,ProcureCatalogID,HospitalID,HospitalName,");
                                sqlTradePre.AppendFormat("CompanyId_PS,CompanyName_PS,OrderId,Amount,PurchaseCount,{0},{1},{2},1,'{3}' from ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("dbo.View_Distribution where DistributeState=0 and WarehouseTime IS NULL AND DistributeTime <='{0}'", DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and CompanyId_PS IN({0})", _CompanyNameId);
                                }

                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and HospitalID IN({0})", _HospitalID);
                                }

                                if (_ProcureCatalogID != "所有药品") { sqlTradePre.AppendFormat(" and ProcureCatalogID='{0}'", _ProcureCatalogID); }
                                sqlTradePre.AppendFormat(" and AreaID='{0}';", _AreaID);
                                break;
                            case "2": //企业已开发票中心未审核
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,c.AreaName,a.HospitalID,a.HospitalName,a.CompanyId_PS,b.CompanyName,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_Invoice a inner join dbo.TB_Company b on a.CompanyID_PS=b.CompanyId join ");
                                sqlTradePre.AppendFormat("TB_Area c on a.AreaID=c.AreaID WHERE a.IsSubmit = 1 AND a.AuditStatus = 0 and AddDate<='{0}'", DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyID_PS IN({0})", _CompanyNameId);
                                }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "3"://医院已提交退货单企业未确认
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.HospitalID,a.HospitalName,a.CompanyId,a.CompanyName,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_ReturnMerchandiseDetail a inner join TB_Area b on a.AreaID=b.AreaID WHERE a.ReturnOrderStatus = 2 AND a.ReturnState = 0 and ReturnTime<='{0}'", System.DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyID_PS IN({0})", _CompanyNameId);
                                }
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0})", _HospitalID);
                                }
                                if (_ProcureCatalogID != "所有药品") { sqlTradePre.AppendFormat(" and a.ProcureCatalogID='{0}'", _ProcureCatalogID); }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "4"://医院已提交回款单企业未确认
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.HospitalID,a.HospitalName,a.CompanyId_PS,a.CompanyName_PS,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_InvoicePay a inner join TB_Area b on a.AreaID=b.AreaID WHERE PayStatus = 0 and a.InvoicePayTime<='{0}' ", System.DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0})", _HospitalID);
                                }
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyID_PS IN({0})", _CompanyNameId);
                                }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "5"://医院已入库未回款
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.HospitalID,a.HospitalName,a.CompanyId_PS,a.CompanyName_PS,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_InvoiceDistribution a inner join TB_Area b on a.AreaID=b.AreaID where a.WarehouseCount > 0 AND and a.IsInvoice = 1 and a.AuditStatus = '1' and a.InvoicePaySerialID IS NULL and a.WarehouseTime<='{0}' ", System.DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0})", _HospitalID);
                                }
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyID_PS IN({0})", _CompanyNameId);
                                }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                        }

                        js++; //每次循环记录次数10次一执行
                        if (gs == performziJian)//判断是不是最后一次的执行
                        {
                            if (i == dtTradePre.Rows.Count - 1)//把最后的数据全部拼接完成
                            {
                                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sqlTradePre.ToString());
                                js = 0;
                                sqlTradePre.Remove(0, sqlTradePre.Length);
                            }
                        }
                        else
                        {
                            if (js == 10)//10次一执行记录
                            {
                                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sqlTradePre.ToString());
                                gs++;
                                js = 0;
                                sqlTradePre.Remove(0, sqlTradePre.Length);
                            }
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < dtTradePre.Rows.Count; i++)
                    {
                        string _AreaID = dtTradePre.Rows[i]["AreaID"].ToString();  //区域编号
                        string _SortID = dtTradePre.Rows[i]["SortID"].ToString();  //类别编号
                        string _ProcureCatalogID = dtTradePre.Rows[i]["ProcureCatalogID"].ToString();//药品编号
                        string _HospitalID = dtTradePre.Rows[i]["HospitalID"].ToString(); //医院编号
                        string _CompanyNameId = dtTradePre.Rows[i]["CompanyId_PS"].ToString(); //企业编号
                        string _WarningLevel = dtTradePre.Rows[i]["WarningLevel"].ToString();  //预警级别
                        int _WarningDate = int.Parse(dtTradePre.Rows[i]["WarningDate"].ToString());//预警天数
                        string _WarningType = dtTradePre.Rows[i]["WarningType"].ToString();//预警类型

                        switch (_WarningType)
                        {
                            case "0":   //医院已提交采购单企业未确认
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,SortId,SortName,ProcureCatalogID,");
                                sqlTradePre.AppendFormat("HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,OrderId,Amount,PurchaseCount,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.SortId,a.SortName,a.ProcureCatalogID,a.HospitalID,a.HospitalName, ");
                                sqlTradePre.AppendFormat("a.CompanyId_PS,a.CompanyName_PS,a.OrderId,a.Amount,a.PurchaseCount,{0},{1},{2},1,'{3}' from ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("View_PurchaseOrderDetail a inner join TB_Area b on a.AreaID=b.AreaID where a.OrderDetailState=1 and a.StartTime <='{0}'", DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0}) ", _HospitalID);
                                }
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyId_PS IN({0})", _CompanyNameId);
                                }
                                if (_ProcureCatalogID != "所有药品") { sqlTradePre.AppendFormat(" and a.ProcureCatalogID={0}", _ProcureCatalogID); }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "1":  //企业配送定单医院未入库
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,SortId,SortName,ProcureCatalogID,HospitalID,HospitalName,");
                                sqlTradePre.AppendFormat("CompanyId_PS,CompanyName_PS,OrderId,Amount,PurchaseCount,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select AreaID,AreaName,SortId,SortName,ProcureCatalogID,HospitalID,HospitalName,");
                                sqlTradePre.AppendFormat("CompanyId_PS,CompanyName_PS,OrderId,Amount,PurchaseCount,{0},{1},{2},1,'{3}' from ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("dbo.View_Distribution where DistributeState=0 and WarehouseTime IS NULL AND DistributeTime <='{0}'", DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and CompanyId_PS IN({0})", _CompanyNameId);
                                }
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and HospitalID IN({0})", _HospitalID);
                                }
                                if (_ProcureCatalogID != "所有药品") { sqlTradePre.AppendFormat(" and ProcureCatalogID='{0}'", _ProcureCatalogID); }
                                sqlTradePre.AppendFormat(" and AreaID='" + _AreaID + "';");
                                break;
                            case "2": //企业已开发票中心未审核
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,c.AreaName,a.HospitalID,a.HospitalName,a.CompanyId_PS,b.CompanyName,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_Invoice a inner join dbo.TB_Company b on a.CompanyID_PS=b.CompanyId join ");
                                sqlTradePre.AppendFormat("TB_Area c on a.AreaID=c.AreaID WHERE a.IsSubmit = 1 AND a.AuditStatus = 0 and InvoiceTime<='{0}'", DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyID_PS IN({0})", _CompanyNameId);
                                }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "3"://医院已提交退货单企业未确认
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,ProcureCatalogID,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.ProcureCatalogID,a.HospitalID,a.HospitalName,a.CompanyId,a.CompanyName,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_ReturnMerchandiseDetail a inner join TB_Area b on a.AreaID=b.AreaID WHERE a.ReturnOrderStatus = 2 AND a.ReturnState = 0 and ReturnTime<='{0}'", System.DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyId IN({0}) ", _CompanyNameId);
                                }
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0}) ", _HospitalID);
                                }

                                if (_ProcureCatalogID != "所有药品") { sqlTradePre.AppendFormat(" and a.ProcureCatalogID={0}", _ProcureCatalogID); }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "4"://医院已提交回款单企业未确认
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.HospitalID,a.HospitalName,a.CompanyId_PS,a.CompanyName_PS,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_InvoicePay a inner join TB_Area b on a.AreaID=b.AreaID WHERE PayStatus = 0 and a.InvoicePayTime<='{0}' ", System.DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0})", _HospitalID);
                                }
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyId_PS IN({0})", _CompanyNameId);
                                }
                                sqlTradePre.AppendFormat(" and a.AreaID='{0}';", _AreaID);
                                break;
                            case "5"://医院已入库未回款
                                sqlTradePre.AppendFormat("insert into jg.TB_WarningData(AreaId,AreaName,HospitalID,HospitalName,CompanyId_PS,CompanyName_PS,WarningLevel,WarningDate,WarningType,[Type],AddTime)");
                                sqlTradePre.AppendFormat("select a.AreaID,b.AreaName,a.HospitalID,a.HospitalName,a.CompanyId_PS,a.CompanyName_PS,{0},{1},{2},1,'{3}' ", _WarningLevel, _WarningDate, _WarningType, System.DateTime.Now);
                                sqlTradePre.AppendFormat("from dbo.View_InvoiceDistribution a inner join TB_Area b on a.AreaID=b.AreaID where a.WarehouseCount > 0 AND a.InvoicePaySerialID IS NULL and a.WarehouseTime<='{0}' ", System.DateTime.Now.AddDays(-1 * _WarningDate));
                                if (_HospitalID != "所有医院")
                                {
                                    _HospitalID = "'" + _HospitalID.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.HospitalID IN({0})", _HospitalID);
                                }
                                if (_CompanyNameId != "所有企业")
                                {
                                    _CompanyNameId = "'" + _CompanyNameId.Replace(" ", "").Replace(",", "','") + "'";
                                    sqlTradePre.AppendFormat(" and a.CompanyId_PS IN({0})", _CompanyNameId);
                                }
                                sqlTradePre.AppendFormat(" and a.AreaID='" + _AreaID + "';");
                                break;
                        }
                    }

                    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sqlTradePre.ToString());
                }
            }
        }

        public static System.Data.DataTable GetWarningArea()
        {
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(@"SELECT AreaID,AreaName, min(WarningLevel) AS WarningLevel FROM jg.TB_WarningData GROUP BY AreaID,AreaName HAVING min(WarningLevel) > 0");
        }

        /// <summary>
        /// 验证采购预警是否重复
        /// </summary>
        /// <param name="drug"></param>
        /// <returns></returns>
        public static bool DrugCheckDataSource(HZ.Supervise.Model.DrugPreWarning drug)
        {
            bool bol = false;
            StringBuilder sbwhere = new StringBuilder();
            if (string.IsNullOrEmpty(drug.WarningCount.ToString()))
            {
                sbwhere.AppendFormat(" and  WarningCount is null ");
            }
            else
            {
                sbwhere.AppendFormat(" and WarningCount={0}", drug.WarningCount);
            }
            if (string.IsNullOrEmpty(drug.WarningAmount.ToString()))
            {
                sbwhere.AppendFormat(" and WarningAmount is null ");
            }
            else
            {
                sbwhere.AppendFormat(" and WarningAmount={0}", drug.WarningAmount);
            }


            string sql = string.Format(@"select DrugPreWarningID from jg.TB_DrugPreWarning where AreaID='{0}' and procureCatalogID='{1}' 
                                       and HospitalID ='{2}' {3}", drug.AreaID, drug.ProcureCatalogID, drug.HospitalID, sbwhere.ToString());



            if (HZ.Data.DbHelperFactory.Create().ExecuteTable(sql).Rows.Count > 0)
            {
                bol = true;
            }

            return bol;
        }

        /// <summary>
        /// 交易过程预警监测
        /// </summary>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static bool TranCheckDataSource(HZ.Supervise.Model.TradePreWarning tran)
        {
            bool bol = false;

            string sql = string.Format(@"select TradePreWarningID from jg.TB_TradePreWarning where AreaID='{0}' and procureCatalogID='{1}' and 
                                        HospitalID='{2}' and CompanyId_PS='{3}' and WarningType='{4}'", tran.AreaID, tran.ProcureCatalogID, tran.HospitalID, tran.CompanyID_PS, tran.WarningType);

            if (HZ.Data.DbHelperFactory.Create().ExecuteTable(sql).Rows.Count > 0)
            {
                bol = true;
            }

            return bol;
        }
    }
}


